package org.link.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

import org.link.bean.Article;
import org.link.util.DBUtil;
import org.link.util.DateUtil;

/**
 * 
 * project_name:forumweb
 * package_name:org.link.dao
 * file_name:ArticleDao.java
 * @author Link
 * create_date:2015年10月27日 下午5:39:19
 * modified_date:
 * description: 
 *
 */
public class ArticleDao {

	/**
	 * 
	 * description: 添加主贴子
	 * creatTime:2015年10月27日下午10:14:18
	 * @author Link
	 * return type: int
	 *
	 */
	public int addMainArticle(int userId,int pbid,int bid,String title,String content,int point){
		int result = 0;
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement("insert into t_article(title,content,pbid,bid,createtime,user_id,point)values(?,?,?,?,?,?,?)");
			pstmt.setString(1, title);
			pstmt.setString(2, content);
			pstmt.setInt(3, pbid);
			pstmt.setInt(4, bid);
			pstmt.setString(5, DateUtil.getDate(new Date()));
			pstmt.setInt(6, userId);
			pstmt.setInt(7, point);
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn);
		}
		return result;
	}
	
	/**
	 * 
	 * description: 添加回帖
	 * creatTime:2015年11月1日上午12:56:48
	 * @author Link
	 * return type: int
	 *
	 */
	public int addReceiptArticle(String content,int main,int userId){
		int result  = 0;
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement("insert into t_article(content,main,user_id,createtime)values(?,?,?,?)");
			pstmt.setString(1, content);
			pstmt.setInt(2, main);
			pstmt.setInt(3, userId);
			pstmt.setString(4, DateUtil.getDate(new Date()));
			result = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn);
		}
		return result;
	}
	
	/**
	 * 
	 * description: 根据输入的主帖子id号查询所有帖子信息并返回()
	 * creatTime:2015年10月27日下午10:26:48
	 * @author Link
	 * return type: ArrayList<Article>
	 *
	 */
	public ArrayList<Article> queryAllArticle(int main){
		ArrayList<Article> articles = new ArrayList<Article>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from t_article where main = ?");
			pstmt.setInt(1, main);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setMain(rs.getInt("main"));
				article.setPbid(rs.getInt("pbid"));
				article.setBid(rs.getInt("bid"));
				article.setCreateTime(rs.getString("createtime"));
				article.setPoint(rs.getInt("point"));
				article.setGood(rs.getInt("good"));
				article.setBad(rs.getInt("bad"));
				article.setUserId(rs.getInt("user_id"));
				articles.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return articles;	
	}
	
	/**
	 * 
	 * description: 根据传入的主贴子id找到所有跟帖的主键id
	 * creatTime:2015年11月1日下午11:21:48
	 * @author Link
	 * return type: ArrayList<Integer>
	 *
	 */
	public ArrayList<Integer> queryRecieptArticleId(int main){
		ArrayList<Integer> articleId = new ArrayList<Integer>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select id from t_article where main = ?");
			pstmt.setInt(1, main);
			rs = pstmt.executeQuery();
			while(rs.next()){
				articleId.add(rs.getInt("id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return articleId;
	}
	
	/**
	 * 
	 * description:根据输入的查询范围查找对应的帖子信息并返回
	 * 2015年10月29日 上午9:12:44
	 * @author Link
	 * @param begin
	 * @param rows
	 * @return
	 *
	 */
	public ArrayList<Article> queryAllArticle(int begin,int rows){
		ArrayList<Article> articles = new ArrayList<Article>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from t_article where main=0 limit ?,?");
			pstmt.setInt(1, begin);
			pstmt.setInt(2, rows);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Article article = new Article();
				article.setId(rs.getInt("id"));
				article.setTitle(rs.getString("title"));
				article.setContent(rs.getString("content"));
				article.setMain(rs.getInt("main"));
				article.setPbid(rs.getInt("pbid"));
				article.setBid(rs.getInt("bid"));
				article.setCreateTime(rs.getString("createtime"));
				article.setPoint(rs.getInt("point"));
				article.setGood(rs.getInt("good"));
				article.setBad(rs.getInt("bad"));
				article.setUserId(rs.getInt("user_id"));
				articles.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return articles;	
	}
	
	/**
	 * 
	 * description:返回帖子总数
	 * 2015年10月29日 上午9:16:34
	 * @author Link
	 * @return
	 *
	 */
	public int queryArticleCount(int main){
		int result = 0;
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select count(id) from t_article where main=?");
			pstmt.setInt(1, main);
			rs = pstmt.executeQuery();
			while(rs.next()){
				result = rs.getInt("count(id)");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return result;
	}

	/**
	 * 
	 * description: 根据主贴id号查询出主贴信息并返回
	 * creatTime:2015年11月1日上午12:01:56
	 * @author Link
	 * return type: Article
	 *
	 */
	public Article queryMainArticle(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Article article = new Article();
		try {
			pstmt = conn.prepareStatement("select * from t_article a,t_user u,t_board b where a.id = ? and a.bid=b.id and a.user_id=u.id");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				article.setId(rs.getInt("a.id"));
				article.setTitle(rs.getString("a.title"));
				article.setContent(rs.getString("a.content"));
				article.setMain(rs.getInt("a.main"));
				article.setPbid(rs.getInt("a.pbid"));
				article.setbName(rs.getString("b.board_name"));//直接板块名称
				article.setBid(rs.getInt("bid"));
				article.setCreateTime(rs.getString("createtime"));
				article.setPoint(rs.getInt("point"));
				article.setGood(rs.getInt("good"));
				article.setBad(rs.getInt("bad"));
				article.setUserId(rs.getInt("user_id"));
				article.setUserName(rs.getString("u.email"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return article;
	}
	
	/**
	 * 
	 * description: 查询并返回所有回帖
	 * creatTime:2015年11月1日下午11:42:44
	 * @author Link
	 * return type: Article
	 *
	 */
	/*public Article queryRecieptArticle(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Article article = new Article();
		try {
			pstmt = conn.prepareStatement("select * from t_article a,t_user u where a.id = ? and a.user_id=u.id");
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				article.setId(rs.getInt("a.id"));
				article.setTitle(rs.getString("a.title"));
				article.setContent(rs.getString("a.content"));
				article.setMain(rs.getInt("a.main"));
				article.setPbid(rs.getInt("a.pbid"));
				article.setBid(rs.getInt("a.bid"));
				article.setCreateTime(rs.getString("a.createtime"));
				article.setPoint(rs.getInt("a.point"));
				article.setGood(rs.getInt("a.good"));
				article.setBad(rs.getInt("a.bad"));
				article.setUserId(rs.getInt("a.user_id"));
				article.setUserName(rs.getString("u.email"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return article;
	} */
	
	public ArrayList<Article> queryRecieptArticle(int main){
		ArrayList<Article> articles = new ArrayList<Article>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = conn.prepareStatement("select * from t_article a,t_user u where a.main = ? and a.user_id=u.id");
			pstmt.setInt(1, main);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Article article = new Article();
				article.setId(rs.getInt("a.id"));
				article.setTitle(rs.getString("a.title"));
				article.setContent(rs.getString("a.content"));
				article.setMain(rs.getInt("a.main"));
				article.setPbid(rs.getInt("a.pbid"));
				article.setBid(rs.getInt("a.bid"));
				article.setCreateTime(rs.getString("a.createtime"));
				article.setPoint(rs.getInt("a.point"));
				article.setGood(rs.getInt("a.good"));
				article.setBad(rs.getInt("a.bad"));
				article.setUserId(rs.getInt("a.user_id"));
				article.setUserName(rs.getString("u.email"));
				articles.add(article);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(pstmt, conn, rs);
		}
		return articles;	
	}
	
	public static void main(String[] args) {
		//System.out.println(new ArticleDao().queryArticleCount(0));
		//System.out.println(new ArticleDao().queryArticle(1));
	}
}






